package muta.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.coderdream.dao.LoggingDao;
import com.coderdream.util.DBUtil;

public class SqlBean {

	private LoggingDao loggingDao = new LoggingDao(this.getClass().getName());

	PreparedStatement pre;
	ResultSet rs;

	/**
	 * @author help
	 * 
	 *         插入新的一条记录
	 * @return
	 */
	public int getInsert(String sql, JavaBean jBean) {
		int count = 0;
		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			pre.setString(1, jBean.getName());
			pre.setString(2, jBean.getPassword());
			pre.setString(3, jBean.getSex());
			pre.setInt(4, jBean.getAge());

			loggingDao.debug("sql" + sql);
			loggingDao.debug("jBean" + jBean);
			count = pre.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return count;
	}

	/**
	 * @author help
	 * 
	 *         删除一条记录
	 * @return
	 */
	public int getDelete(String sql, int id) {
		int count = 0;
		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);

			loggingDao.debug("sql" + sql);
			count = pre.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return count;
	}

	/**
	 * @author help
	 * 
	 *         根据ID查询某一条记录
	 * @return
	 */
	public JavaBean getSearchById(String sql, int id) {
		JavaBean jBean = new JavaBean();

		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			rs = pre.executeQuery();

			loggingDao.debug("sql" + sql);
			while (rs.next()) {
				jBean.setId(rs.getInt("id"));
				jBean.setName(rs.getString("name"));
				jBean.setPassword(rs.getString("password"));
				jBean.setSex(rs.getString("sex"));
				jBean.setAge(rs.getInt("age"));

				loggingDao.debug("jBean" + jBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return jBean;
	}

	/**
	 * @author help
	 * 
	 *         更新某一条记录
	 * @return
	 */
	public int getUpdate(String sql, JavaBean jBean) {
		int count = 0;
		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(5, jBean.getId());
			pre.setString(1, jBean.getName());
			pre.setString(2, jBean.getPassword());
			pre.setString(3, jBean.getSex());
			pre.setInt(4, jBean.getAge());
			count = pre.executeUpdate();

			loggingDao.debug("sql" + sql);
			loggingDao.debug("jBean" + jBean);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return count;
	}

	/**
	 * @author help
	 * 
	 *         显示所有记录
	 * @return
	 */
	public List<JavaBean> getSearch(String sql) {
		List<JavaBean> list = new ArrayList<JavaBean>();

		// 获取prepareStatement对象
		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();

			loggingDao.debug("sql" + sql);
			while (rs.next()) {
				JavaBean jBean = new JavaBean();
				jBean.setId(rs.getInt("id"));
				jBean.setName(rs.getString("name"));
				jBean.setPassword(rs.getString("password"));
				jBean.setSex(rs.getString("sex"));
				jBean.setAge(rs.getInt("age"));

				loggingDao.debug("jBean" + jBean);
				list.add(jBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return list;
	}
}